I use Hibernate 5.4.32.Final, when I try to execute this query via spring data:
@Query("select new some.package.dto.TwoFieldDto(o.gln, u.email) "
+ "from OrganizationEntity o "
+ "join UserEntity u on u.organization.id = o.id "
+ "where o.gln in (:glnList)")
List<TwoFieldDto<String, String>> findEmailListByGlnIn(List<String> glnList);
On starting application Hibernate throw SyntexException when it reaches to UserEntity token, and gives out unexpected token.
If I try to execute query from the UserEntity side, query compiles successfully. (Yes, I haven't get a link from OrganizationEntity to UserEntity)
Is this Hibernate version not supported JOIN ON syntex? (OrganizationEntity doesn't contain link on UserEntity, but UserEntity has it).
The @Queryannotation expects JPQL by default which has its own syntax. As far as I know, you can not do someting like JOIN .. ON in JPQL .
I do not know the association between your entities but it should look someting like this:
@Query("select new by.urspectr.ediprov.util.dto.TwoFieldDto(o.gln, u.email) "
+ "from OrganizationEntity o "
+ "join o.UserEntity u "
+ "where o.gln in (:glnList)")
But for that to work, there has to be an association between OrganizationEntity and UserEntity.
As an alternative you can always use a native query, where you could do a JOIN .. ON in your native SQL dialect:
@Query(value = "SELECT ....", nativeQuery = true)
But this should only be an option if you are sure that you can not do it with JPQL.